package com.egao.orderB.biz.impl;

import com.egao.common.core.damon.constant.UserConstant;
import com.egao.common.core.damon.results.BaseDao;
import com.egao.common.core.damon.results.PageResult2;
import com.egao.common.core.damon.utils.Utils;
import com.egao.common.core.web.JsonResult;
import com.egao.common.system.entity.User;
import com.egao.orderB.biz.BillFeeBiz;
import com.egao.orderB.entity.Bill;
import com.egao.orderB.entity.BillFee;
import com.egao.orderB.entity.BillFeeWriteOffItem;
import org.apache.poi.ss.formula.functions.T;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SingleColumnRowMapper;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 数据层实现类
 *
 * @author : Doman
 * @version ：1.0
 * @date : 2020-06-15 16:11:54
 */
@Repository
public class BillFeeBizImpl extends BaseDao implements BillFeeBiz {

  @Override
  public JsonResult list(PageResult2<T> page, BillFee billFee, User loginUser) throws Exception {
    Map<String, String> map = new HashMap<String, String>();
    RowMapper<BillFee> rm = new BeanPropertyRowMapper<BillFee>(BillFee.class);
    StringBuffer sql = new StringBuffer("select b.id,b.organization_id,b.bill_id");
    sql.append(",b.type,b.fee_name,b.standard,b.price," +
            "b.fee_state,b.fee_person,b.fee_time,b.fobject,right(b.fee_modules,char_length(b.fee_modules)-1) fee_modules,b.remark");
    sql.append(",b.invoiceid,b.invoicenum,b.invoicename,CASE b.driver_name WHEN null THEN d.name WHEN '' THEN d.name ELSE b.driver_name END driverName,b.invoicestate,b.currency,b.exchange_rate ");
    sql.append(",b.create_time,b.create_user,b.update_time,b.update_user");
    sql.append(",sognz.organization_name organizationName,suser.nick_name createName ");
    sql.append(" from chf_bill_fee b ");
    sql.append(" inner join sys_user suser on suser.user_id=b.create_user ");
    sql.append(" left join chf_driver d on d.car_no=b.invoicename and d.deleted=0 ");
    sql.append(" inner join sys_organization sognz on sognz.organization_id=b.organization_id ");
    sql.append(" where b.deleted=0 and sognz.deleted=0 and b.bill_id=:bill_id ");
    map.put("bill_id", billFee.getBillId());
    if (loginUser.getUserId() != UserConstant.ADMIN_ID) {
      sql.append(" and b.organization_id=:tableOrganizationId ");
      map.put("tableOrganizationId", loginUser.getOrganizationId() + "");
    }
    if (Utils.isNotEmpty(billFee)) {
      if (Utils.isNotEmpty(billFee.getType())) {
        sql.append(" and instr(b.type,:type)>0 ");
        map.put("type", billFee.getType());
      }
      if (Utils.isNotEmpty(billFee.getFeeName())) {
        sql.append(" and instr(b.fee_name,:fee_name)>0 ");
        map.put("fee_name", billFee.getFeeName());
      }
      if (Utils.isNotEmpty(billFee.getFeeState())) {
        sql.append(" and instr(b.fee_state,:fee_state)>0 ");
        map.put("fee_state", billFee.getFeeState());
      }
      if (Utils.isNotEmpty(billFee.getFobject())) {
        sql.append(" and instr(b.fobject,:fobject)>0 ");
        map.put("fobject", billFee.getFobject());
      }
      if (Utils.isNotEmpty(billFee.getSort()) && Utils.isNotEmpty(billFee.getOrder())) {
        sql.append(" order by " + billFee.getSort() + " " + billFee.getOrder());
      }
    }
    return jsonResult(getPageQueryList(page, sql.toString(), map, rm));
  }
  @Override
  public JsonResult listByFeeName(PageResult2<T> page, BillFee billFee, User loginUser) throws Exception {
    Map<String, String> map = new HashMap<String, String>();
    RowMapper<BillFee> rm = new BeanPropertyRowMapper<BillFee>(BillFee.class);
    StringBuffer sql = new StringBuffer("");
    sql.append("select b.pack_date,b.bill_of_lading,b.box_num,f.fee_name,if(f.driver_name <>'', f.driver_name ,f.invoicename) invoicename, f.price ");
    sql.append(" from chf_bill b ");
    sql.append(" inner join chf_bill_fee f on b.id=f.bill_id ");
    sql.append(" where b.deleted=0 ");

    if (Utils.isNotEmpty(billFee.getDate1())) {
      sql.append(" and b.pack_date>= :date1 ");
      map.put("date1", billFee.getDate1());
    }
    if (Utils.isNotEmpty(billFee.getDate2())) {
      sql.append(" and b.pack_date<=:date2 ");
      map.put("date2", billFee.getDate2());
    }
    if (Utils.isNotEmpty(billFee.getFeeName())) {
      sql.append(" and f.fee_name=:fee_name ");
      map.put("fee_name", billFee.getFeeName());
    }
    if (Utils.isNotEmpty(billFee.getInvoicename())) {
      sql.append(" and (f.driver_name=:invoicename or f.invoicename=:invoicename ) ");
      map.put("invoicename", billFee.getInvoicename());
    }
    return jsonResult(getPageQueryList(page, sql.toString(), map, rm));
  }

  @Override
  public List<BillFee> list(BillFee billFee, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    RowMapper<BillFee> rm = new BeanPropertyRowMapper<BillFee>(BillFee.class);
    StringBuffer sql = new StringBuffer();
    sql.append("select  ");
    sql.append("cc.id,cc.organization_id,cc.bill_id,cc.type");
    sql.append(",cc.fee_name");
    sql.append(",cc.standard,cc.price,cc.fee_state,cc.fee_person,cc.fee_time,cc.remark,cc.fobject,cc.fee_modules");
    sql.append(",cc.invoiceid,cc.invoicenum,cc.invoicename,cc.driverName,cc.invoicestate,cc.currency,cc.exchange_rate ");
    sql.append(",cc.create_time,cc.create_user,cc.update_time,cc.update_user");
    sql.append(",cc.organizationName,cc.createName ");
    sql.append(",cc.updateName ");
    sql.append(" from(");
    sql.append("select b.id,b.organization_id,b.bill_id,b.type");
    sql.append(",b.fee_name");
    sql.append(",b.standard,b.price,b.fee_state,b.fee_person,b.fee_time,b.remark,b.fobject,right(b.fee_modules,char_length(b.fee_modules)-1) fee_modules,b.fee_modules fee_modules2");
    sql.append(",b.invoiceid,b.invoicenum,b.invoicename,CASE b.driver_name WHEN null THEN d.name WHEN '' THEN d.name  ELSE b.driver_name END driverName,b.invoicestate,b.currency,b.exchange_rate ");
    sql.append(",b.create_time,b.create_user,b.update_time,b.update_user");
    sql.append(",sognz.organization_name organizationName,suser.nick_name createName ");
    sql.append(",suser2.nick_name updateName ");
    sql.append(" from chf_bill_fee b ");
    sql.append(" inner join sys_user suser on suser.user_id=b.create_user ");
    sql.append(" left join sys_user suser2 on suser2.user_id=b.update_user ");
    sql.append(" left join chf_driver d on d.car_no=b.invoicename and d.deleted=0 ");
    sql.append(" inner join sys_organization sognz on sognz.organization_id=b.organization_id and sognz.deleted=0 ");
    sql.append(" where b.deleted=0 and b.bill_id=:bill_id ");
    map.put("bill_id", billFee.getBillId());
    if (loginUser.getUserId() != UserConstant.ADMIN_ID) {
      sql.append(" and b.organization_id=:tableOrganizationId ");
      map.put("tableOrganizationId", loginUser.getOrganizationId() + "");
    }
    if (Utils.isNotEmpty(billFee)) {
      if (Utils.isNotEmpty(billFee.getType())) {
        sql.append(" and instr(b.type,:type)>0 ");
        map.put("type", billFee.getType());
      }
      if (Utils.isNotEmpty(billFee.getInvoicename())) {
        sql.append(" and instr(b.invoicename,:invoicename)>0 ");
        map.put("invoicename", billFee.getInvoicename());
      }
      if (Utils.isNotEmpty(billFee.getFeeName())) {
        sql.append(" and instr(b.fee_name,:fee_name)>0 ");
        map.put("fee_name", billFee.getFeeName());
      }
      if (Utils.isNotEmpty(billFee.getFobject())) {
        sql.append(" and instr(b.fobject,:fobject)>0 ");
        map.put("fobject", billFee.getFobject());
      }
      if (Utils.isNotEmpty(billFee.getFeeState())) {
        sql.append(" and instr(b.fee_state,:fee_state)>0 ");
        map.put("fee_state", billFee.getFeeState());
      }
    }
    sql.append(" order by fee_modules2 asc, CONVERT(b.fee_name using gbk) desc");
    sql.append(" ) cc ");
    getMapParameter(sql, map);
    return jdbcTemplate.query(sql.toString(), map, rm);
  }

  @Override
  public Integer chartTotalFee(BillFee billFee, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    RowMapper<Integer> rm = new SingleColumnRowMapper<Integer>(Integer.class);
    StringBuffer sql = new StringBuffer();
    sql.append("select  ");
    //全部
    if (Utils.isEmpty(billFee.getSettlement())) {
      sql.append(" sum(bf.price) price ");
      //未销账
    } else if (Utils.isNotEmpty(billFee.getSettlement()) && billFee.getSettlement().equals("1")) {
      sql.append(" sum(CASE bf.settlement WHEN 0 THEN bf.price WHEN 1 THEN bf.settlement_price ELSE 0 END) price  ");
      //已销账
    } else if (Utils.isNotEmpty(billFee.getSettlement()) && billFee.getSettlement().equals("2")) {
      sql.append(" sum(bf.price) price ");
    }
    sql.append(" from chf_bill_fee bf ");
    sql.append(" where bf.deleted=0 and invoicename=:invoicename");
    map.put("invoicename", billFee.getInvoicename());
    //未销账
    if (Utils.isNotEmpty(billFee.getSettlement()) && billFee.getSettlement().equals("1")) {
      sql.append(" and bf.settlement!=2 ");
      map.put("settlement", billFee.getSettlement());
    }
    //已销账
    if (Utils.isNotEmpty(billFee.getSettlement()) && billFee.getSettlement().equals("2")) {
      sql.append(" and bf.settlement=2 ");
      map.put("settlement", billFee.getSettlement());
    }
    getMapParameter(sql, map);
    return jdbcTemplate.queryForObject(sql.toString(), map, rm);
  }

  @Override
  public List<BillFee> chartTotalMonthFee(BillFee billFee, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    RowMapper<BillFee> rm = new BeanPropertyRowMapper<BillFee>(BillFee.class);
    StringBuffer sql = new StringBuffer();
    sql.append("select  ");
    //全部
    if (Utils.isEmpty(billFee.getSettlement())) {
      sql.append(" DATE_FORMAT(b.pack_date,'%Y-%m') createTime,sum(bf.price) price ");
      //未销账
    } else if (Utils.isNotEmpty(billFee.getSettlement()) && billFee.getSettlement().equals("1")) {
      sql.append(" DATE_FORMAT(b.pack_date,'%Y-%m') createTime, sum(CASE bf.settlement WHEN 0 THEN bf.price WHEN 1 THEN bf.settlement_price ELSE 0 END) price  ");
      //已销账
    } else if (Utils.isNotEmpty(billFee.getSettlement()) && billFee.getSettlement().equals("2")) {
      sql.append(" DATE_FORMAT(b.pack_date,'%Y-%m') createTime, sum(bf.price) price ");
    }
    sql.append(" from chf_bill_fee bf ");
    sql.append(" inner join chf_bill b on b.id=bf.bill_id ");
    sql.append(" where bf.deleted=0 ");
//		sql.append(" and DATE_FORMAT(b.pack_date,'%Y-%m')=DATE_FORMAT(now(),'%Y-%m') ");
    sql.append(" and invoicename=:invoicename");
    map.put("invoicename", billFee.getInvoicename());
    //未销账
    if (Utils.isNotEmpty(billFee.getSettlement()) && billFee.getSettlement().equals("1")) {
      sql.append(" and bf.settlement!=2 ");
      map.put("settlement", billFee.getSettlement());
    }
    //已销账
    if (Utils.isNotEmpty(billFee.getSettlement()) && billFee.getSettlement().equals("2")) {
      sql.append(" and bf.settlement=2 ");
      map.put("settlement", billFee.getSettlement());
    }
    sql.append(" group by createTime ");
    sql.append(" order by  createTime ");
//		sql.append(" limit 12 ");
    getMapParameter(sql, map);
    return jdbcTemplate.query(sql.toString(), map, rm);
  }

  @Override
  public List<BillFee> exportList(BillFee billFee, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    RowMapper<BillFee> rm = new BeanPropertyRowMapper<BillFee>(BillFee.class);
    StringBuffer sql = new StringBuffer("select distinct b.id,b.organization_id,b.bill_id,b.type");
    sql.append(",CASE WHEN REGEXP_INSTR(b.fee_name, '^提')  THEN '提箱'  WHEN REGEXP_INSTR(b.fee_name, '^进')  THEN '进港' ELSE b.fee_name END as fee_name");
    sql.append(",b.standard,b.price,b.fee_state,b.fee_person,b.fee_time,b.remark,b.fobject,right(b.fee_modules,char_length(b.fee_modules)-1) fee_modules");
    sql.append(",b.invoiceid,b.invoicenum,b.invoicename,CASE b.driver_name WHEN null THEN d.name WHEN '' THEN d.name ELSE b.driver_name END driverName,b.invoicestate,b.currency,b.exchange_rate ");
    sql.append(",b.create_time,b.create_user,b.update_time,b.update_user");
    sql.append(",sognz.organization_name organizationName,suser.nick_name createName ");
    sql.append(",suser2.nick_name updateName ");
    sql.append(" from chf_bill_fee b ");
    sql.append(" inner join sys_user suser on suser.user_id=b.create_user ");
    sql.append(" left join sys_user suser2 on suser2.user_id=b.update_user ");
    sql.append(" left join chf_driver d on d.car_no=b.invoicename and d.deleted=0 ");
    sql.append(" inner join sys_organization sognz on sognz.organization_id=b.organization_id and sognz.deleted=0 ");
    sql.append(" where b.deleted=0 and b.bill_id=:bill_id ");
    map.put("bill_id", billFee.getBillId());
    if (loginUser.getUserId() != UserConstant.ADMIN_ID) {
      sql.append(" and b.organization_id=:tableOrganizationId ");
      map.put("tableOrganizationId", loginUser.getOrganizationId() + "");
    }
    if (Utils.isNotEmpty(billFee)) {
      if (Utils.isNotEmpty(billFee.getType())) {
        sql.append(" and instr(b.type,:type)>0 ");
        map.put("type", billFee.getType());
      }
      if (Utils.isNotEmpty(billFee.getInvoicename())) {
        sql.append(" and b.invoicename=:invoicename ");
        map.put("invoicename", billFee.getInvoicename());
      }
      if (Utils.isNotEmpty(billFee.getFeeName())) {
        sql.append(" and b.fee_name=:fee_name ");
        map.put("fee_name", billFee.getFeeName());
      }
      if (Utils.isNotEmpty(billFee.getFobject())) {
        sql.append(" and b.fobject=:fobject ");
        map.put("fobject", billFee.getFobject());
      }
      if (Utils.isNotEmpty(billFee.getFeeState())) {
        sql.append(" and instr(b.fee_state,:fee_state)>0 ");
        map.put("fee_state", billFee.getFeeState());
      }
    }
    getMapParameter(sql, map);
    return jdbcTemplate.query(sql.toString(), map, rm);
  }

  @Override
  public List<BillFee> exportList2(BillFee billFee, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    RowMapper<BillFee> rm = new BeanPropertyRowMapper<BillFee>(BillFee.class);
    StringBuffer sql = new StringBuffer("select distinct b.id,b.organization_id,b.bill_id,b.type");
    sql.append(",CASE WHEN REGEXP_INSTR(b.fee_name, '^提')  THEN '提箱'  WHEN REGEXP_INSTR(b.fee_name, '^进')  THEN '进港' ELSE b.fee_name END as fee_name");
    sql.append(",b.standard,b.price,b.fee_state,b.fee_person,b.fee_time,b.remark,b.fobject,right(b.fee_modules,char_length(b.fee_modules)-1) fee_modules");
    sql.append(",b.invoiceid,b.invoicenum,b.invoicename,CASE b.driver_name WHEN null THEN d.name WHEN '' THEN d.name ELSE b.driver_name END driverName,b.invoicestate,b.currency,b.exchange_rate ");
    sql.append(",b.create_time,b.create_user,b.update_time,b.update_user");
    sql.append(",sognz.organization_name organizationName,suser.nick_name createName ");
    sql.append(",suser2.nick_name updateName ");
    sql.append(" from chf_bill_fee b ");
    sql.append(" inner join sys_user suser on suser.user_id=b.create_user ");
    sql.append(" left join sys_user suser2 on suser2.user_id=b.update_user ");
    sql.append(" left join chf_driver d on d.car_no=b.invoicename and d.deleted=0 ");
    sql.append(" inner join sys_organization sognz on sognz.organization_id=b.organization_id and sognz.deleted=0 ");
    sql.append(" where b.deleted=0 and b.bill_id=:bill_id ");
    map.put("bill_id", billFee.getBillId());
    if (loginUser.getUserId() != UserConstant.ADMIN_ID) {
      sql.append(" and b.organization_id=:tableOrganizationId ");
      map.put("tableOrganizationId", loginUser.getOrganizationId() + "");
    }
    if (Utils.isNotEmpty(billFee)) {
      if (Utils.isNotEmpty(billFee.getOrganizationName())) {
        sql.append(" and ((instr(b.type,'应收')>0 and b.fee_name='运费') or (instr(b.type,'应付')>0 and b.invoicename='甩手温州')) ");
      }
      if (Utils.isNotEmpty(billFee.getType())) {
        sql.append(" and instr(b.type,:type)>0 ");
        map.put("type", billFee.getType());
      }
      if (Utils.isNotEmpty(billFee.getInvoicename())) {
        sql.append(" and b.invoicename=:invoicename ");
        map.put("invoicename", billFee.getInvoicename());
      }
      if (Utils.isNotEmpty(billFee.getFeeName())) {
        sql.append(" and b.fee_name=:fee_name ");
        map.put("fee_name", billFee.getFeeName());
      }
      if (Utils.isNotEmpty(billFee.getFobject())) {
        sql.append(" and b.fobject=:fobject ");
        map.put("fobject", billFee.getFobject());
      }
      if (Utils.isNotEmpty(billFee.getFeeState())) {
        sql.append(" and instr(b.fee_state,:fee_state)>0 ");
        map.put("fee_state", billFee.getFeeState());
      }
    }
    getMapParameter(sql, map);
    return jdbcTemplate.query(sql.toString(), map, rm);
  }

  @Override
  public List<BillFee> exportListClassifyList(BillFee billFee, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    RowMapper<BillFee> rm = new BeanPropertyRowMapper<BillFee>(BillFee.class);
    StringBuffer sql = new StringBuffer("select bb.type, bb.price,bb.priceNoTax,right(bb.fee_modules,char_length(bb.fee_modules)-1) fee_modules from (");
    sql.append("select b.type,b.fee_modules,sum(b.price) price,sum(" +
            "if(b.invoicestate=1,ROUND(if(instr(',赵正兵,刘德全,',b.invoicename)>0,b.price*" + UserConstant.SZ_INVOICENUM + ",b.price/" + UserConstant.INVOICENUM + "),2),price)" +
            ") priceNoTax");
    sql.append(" from chf_bill_fee b ");
    sql.append(" where b.deleted=0 and b.bill_id=:bill_id ");
    map.put("bill_id", billFee.getBillId());
    if (Utils.isNotEmpty(billFee)) {
      if (Utils.isNotEmpty(billFee.getType())) {
        sql.append(" and instr(b.type,:type)>0 ");
        map.put("type", billFee.getType());
      }
      if (Utils.isNotEmpty(billFee.getInvoicename())) {
        sql.append(" and b.invoicename=:invoicename ");
        map.put("invoicename", billFee.getInvoicename());
      }
      if (Utils.isNotEmpty(billFee.getFeeName())) {
        sql.append(" and b.fee_name=:fee_name ");
        map.put("fee_name", billFee.getFeeName());
      }
      if (Utils.isNotEmpty(billFee.getFobject())) {
        sql.append(" and instr(b.fobject,:fobject)>0 ");
        map.put("fobject", billFee.getFobject());
      }
      if (Utils.isNotEmpty(billFee.getFeeState())) {
        sql.append(" and instr(b.fee_state,:fee_state)>0 ");
        map.put("fee_state", billFee.getFeeState());
      }
    }
    sql.append(" group by b.type ,b.fee_modules ");
    sql.append(" order by b.fee_modules ");
    sql.append(" ) bb");
    getMapParameter(sql, map);
    return jdbcTemplate.query(sql.toString(), map, rm);
  }

  @Override
  public List<String> feeNames(Bill bill, List<String> feeNames, User loginUser) {
    Map<String, Object> map = new HashMap<String, Object>();
    RowMapper<String> rm = new SingleColumnRowMapper<String>(String.class);
    StringBuffer sql = new StringBuffer("select distinct ");
    sql.append(" CASE WHEN REGEXP_INSTR(bf.fee_name, '^提')  THEN '提箱'  WHEN REGEXP_INSTR(bf.fee_name, '^进')  THEN '进港' ELSE bf.fee_name END as fee_name");
    sql.append(" from chf_bill_fee bf ");
    sql.append(" inner join chf_bill b on b.id=bf.bill_id and b.deleted=0 ");
    sql.append(" and instr(',发起费用清单,应付费用已审核,应收费用已审核,财务应收应付费用已审核,结单,',b.state)>0 ");
    sql.append(" where bf.deleted=0 ");
    if (Utils.isNotEmpty(bill)) {
      if (Utils.isNotEmpty(feeNames)) {
        sql.append(" and bf.fee_name not in (:fee_name) ");
        map.put("fee_name", feeNames);
      }
      if (Utils.isNotEmpty(bill.getBillOfLading())) {
        sql.append(" and instr(b.bill_of_lading,:bill_of_lading)>0 ");
        map.put("bill_of_lading", bill.getBillOfLading());
      }
      if (Utils.isNotEmpty(bill.getBoxNum())) {
        sql.append(" and instr(b.box_num,:box_num)>0 ");
        map.put("box_num", bill.getBoxNum());
      }
//      if (Utils.isNotEmpty(bill.getOrganizationName())) {
//        sql.append(" and bf.fee_name=:organizationName");
//        map.put("organizationName", bill.getOrganizationName());
//      }
//      if (Utils.isNotEmpty(bill.getOrganizationId())) {
//        sql.append(" and bf.invoicename=:organizationId");
//        map.put("organizationId", bill.getOrganizationId());
//      }
      if (Utils.isNotEmpty(bill.getDate1())) {
        sql.append(" and b.pack_date>= :date1 ");
        map.put("date1", bill.getDate1());
      }
      if (Utils.isNotEmpty(bill.getDate2())) {
        sql.append(" and b.pack_date<=:date2 ");
        map.put("date2", bill.getDate2());
      }
      if (Utils.isNotEmpty(bill.getKeywords())) {
        sql.append(" and instr(b.customer,:customer)>0 ");
        map.put("customer", bill.getKeywords());
      }
      if (Utils.isNotEmpty(bill.getCustomer())) {
        sql.append(" and bf.invoicename=b.customer ");
        sql.append(" and instr(b.customer,:customer)>0 ");
        map.put("customer", bill.getCustomer());
      }
      if (Utils.isNotEmpty(bill.getCarTeam())) {
        sql.append(" and bf.invoicename=b.car_team ");
        sql.append(" and instr(b.car_team,:car_team)>0 ");
        map.put("car_team", bill.getCarTeam());
      }
      if (Utils.isNotEmpty(bill.getCarNo())) {
        sql.append(" and bf.invoicename=b.car_no ");
        sql.append(" and instr(b.car_no,:car_no)>0 ");
        map.put("car_no", bill.getCarNo());
      }
      if (Utils.isNotEmpty(bill.getDriver())) {
        sql.append(" and bf.driver_name=b.driver ");
        sql.append(" and instr(b.driver,:driver)>0 ");
        map.put("driver", bill.getDriver());
      }
      if (Utils.isNotEmpty(bill.getInvoicename())) {
        sql.append(" and instr(bf.invoicename,:invoicename)>0 ");
        map.put("invoicename", bill.getInvoicename());
      }
      if (Utils.isNotEmpty(bill.getType())) {
        sql.append(" and instr(bf.type,:type)>0 ");
        map.put("type", bill.getType());
      }
    }
    getMapParameter(sql, null);
    return jdbcTemplate.query(sql.toString(), map, rm);
  }

  @Override
  public List<String> feeNamesBillAndApply(Bill bill, List<String> feeNames, User loginUser) {
    Map<String, Object> map = new HashMap<String, Object>();
    RowMapper<String> rm = new SingleColumnRowMapper<String>(String.class);
    StringBuffer sql = new StringBuffer("select distinct ");
    sql.append(" CASE WHEN REGEXP_INSTR(bf.fee_name, '^提')  THEN '提箱'  WHEN REGEXP_INSTR(bf.fee_name, '^进')  THEN '进港' ELSE bf.fee_name END as fee_name");
    sql.append(" from chf_bill_fee bf ");
    sql.append(" inner join chf_bill b on b.id=bf.bill_id and b.deleted=0 ");
    sql.append(" and instr(',发起费用清单,应付费用已审核,应收费用已审核,财务应收应付费用已审核,结单,',b.state)>0 ");
    sql.append(" where bf.deleted=0 ");
    if (Utils.isNotEmpty(bill)) {
      if (Utils.isNotEmpty(feeNames)) {
        sql.append(" and bf.fee_name not in (:fee_name) ");
        map.put("fee_name", feeNames);
      }
      if (Utils.isNotEmpty(bill.getBillOfLading())) {
        sql.append(" and instr(b.bill_of_lading,:bill_of_lading)>0 ");
        map.put("bill_of_lading", bill.getBillOfLading());
      }
      if (Utils.isNotEmpty(bill.getBoxNum())) {
        sql.append(" and instr(b.box_num,:box_num)>0 ");
        map.put("box_num", bill.getBoxNum());
      }
      if (Utils.isNotEmpty(bill.getDate1())) {
        sql.append(" and b.pack_date>= :date1 ");
        map.put("date1", bill.getDate1());
      }
      if (Utils.isNotEmpty(bill.getDate2())) {
        sql.append(" and b.pack_date<=:date2 ");
        map.put("date2", bill.getDate2());
      }
      if (Utils.isNotEmpty(bill.getKeywords())) {
        sql.append(" and instr(b.customer,:customer)>0 ");
        map.put("customer", bill.getKeywords());
      }
      if (Utils.isNotEmpty(bill.getCustomer())) {
        sql.append(" and bf.invoicename=b.customer ");
        sql.append(" and instr(b.customer,:customer)>0 ");
        map.put("customer", bill.getCustomer());
      }
      if (Utils.isNotEmpty(bill.getCarTeam())) {
        sql.append(" and bf.invoicename=b.car_team ");
        sql.append(" and instr(b.car_team,:car_team)>0 ");
        map.put("car_team", bill.getCarTeam());
      }
      if (Utils.isNotEmpty(bill.getCarNo())) {
        sql.append(" and bf.invoicename=b.car_no ");
        sql.append(" and instr(b.car_no,:car_no)>0 ");
        map.put("car_no", bill.getCarNo());
      }
      if (Utils.isNotEmpty(bill.getDriver())) {
        sql.append(" and bf.driver_name=b.driver ");
        sql.append(" and instr(b.driver,:driver)>0 ");
        map.put("driver", bill.getDriver());
      }
      if (Utils.isNotEmpty(bill.getInvoicename())) {
        sql.append(" and instr(bf.invoicename,:invoicename)>0 ");
        map.put("invoicename", bill.getInvoicename());
      }
      if (Utils.isNotEmpty(bill.getType())) {
        sql.append(" and instr(bf.type,:type)>0 ");
        map.put("type", bill.getType());
      }
    }
    sql.append(" union ");
    sql.append(" select distinct ");
    sql.append(" acf.fee_name");
    sql.append(" from chf_bill b ");
    sql.append(" inner join chf_bill_ocean_apply boa on boa.bill_id=b.id ");
    sql.append(" inner join chf_apply_customs ac on ac.id=boa.apply_id and ac.deleted=0 ");
    sql.append(" inner join chf_apply_customs_fee acf on ac.id=acf.apply_id and ac.deleted=0 ");
    sql.append(" and instr(',发起费用清单,应付费用已审核,应收费用已审核,财务应收应付费用已审核,结单,',b.state)>0 ");
    sql.append(" where b.deleted=0 ");
    if (Utils.isNotEmpty(bill)) {
      if (Utils.isNotEmpty(bill.getBillOfLading())) {
        sql.append(" and instr(b.bill_of_lading,:bill_of_lading)>0 ");
        map.put("bill_of_lading", bill.getBillOfLading());
      }
      if (Utils.isNotEmpty(bill.getBoxNum())) {
        sql.append(" and instr(b.box_num,:box_num)>0 ");
        map.put("box_num", bill.getBoxNum());
      }
      if (Utils.isNotEmpty(bill.getDate1())) {
        sql.append(" and b.pack_date>= :date1 ");
        map.put("date1", bill.getDate1());
      }
      if (Utils.isNotEmpty(bill.getDate2())) {
        sql.append(" and b.pack_date<=:date2 ");
        map.put("date2", bill.getDate2());
      }
      if (Utils.isNotEmpty(bill.getKeywords())) {
        sql.append(" and instr(b.customer,:customer)>0 ");
        map.put("customer", bill.getKeywords());
      }
      if (Utils.isNotEmpty(bill.getCustomer())) {
        sql.append(" and acf.invoicename=:invoicename ");
        map.put("invoicename", bill.getCustomer());
      }
    }
    getMapParameter(sql, null);
    return jdbcTemplate.query(sql.toString(), map, rm);
  }

  @Override
  public BillFee getById(BillFee billFee) {
    RowMapper<BillFee> rm = new BeanPropertyRowMapper<BillFee>(BillFee.class);
    Map<String, String> map = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("select b.id,b.organization_id,b.bill_id");
    sql.append(",b.type,b.fee_name,b.standard,b.price" +
            ",b.fee_state,b.fee_person,b.fee_time,b.remark,b.fobject,right(b.fee_modules,char_length(b.fee_modules)-1) fee_modules");
    sql.append(",b.invoiceid,b.invoicename,CASE b.driver_name WHEN null THEN d.name WHEN '' THEN d.name  ELSE b.driver_name END driverName,b.invoicestate,b.currency,b.exchange_rate ");
    sql.append(",b.create_time,b.create_user,b.update_time,b.update_user");
    sql.append(",sognz.organization_name organizationName,suser.nick_name createName ");
    sql.append(" from chf_bill_fee b ");
    sql.append(" inner join sys_user suser on suser.user_id=b.create_user ");
    sql.append(" left join chf_driver d on d.car_no=b.invoicename and d.deleted=0 ");
    sql.append(" inner join sys_organization sognz on sognz.organization_id=b.organization_id ");
    sql.append(" where b.deleted=0 and sognz.deleted=0 and b.id=:id ");
    map.put("id", billFee.getId());
    getMapParameter(sql, map);
    return jdbcTemplate.queryForObject(sql.toString(), map, rm);
  }

  @Override
  public boolean save(BillFee billFee, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("insert into chf_bill_fee (");
    sql.append("organization_id,bill_id,type,fee_name");
    sql.append(",invoiceid,invoicenum,invoicename,driver_name,invoicestate,currency,exchange_rate");
    sql.append(",price,standard,fobject,fee_modules,remark,create_user,create_time) values ( ");
    sql.append(":organization_id,:bill_id,:type,:fee_name");
    sql.append(",:invoiceid,:invoicenum,:invoicename,:driver_name,:invoicestate,:currency,:exchange_rate");
    sql.append(",:price,:standard,:fobject,:fee_modules,:remark,:create_user,now())");
    map.put("organization_id", loginUser.getOrganizationId() + "");
    map.put("bill_id", billFee.getBillId());
    map.put("type", billFee.getType());
    map.put("fee_name", billFee.getFeeName());
    map.put("invoicename", billFee.getInvoicename());
    map.put("driver_name", billFee.getDriverName());
    map.put("invoicestate", billFee.getInvoicestate());
    map.put("currency", billFee.getCurrency());
    map.put("invoiceid", billFee.getInvoiceid());
    map.put("invoicenum", billFee.getInvoicenum());
    map.put("exchange_rate", billFee.getExchangeRate());
    map.put("price", billFee.getPrice());
    map.put("standard", billFee.getStandard());
    map.put("remark", billFee.getRemark());
    map.put("fobject", billFee.getFobject());
    map.put("fee_modules", billFee.getFeeModules2());
    map.put("create_user", loginUser.getUserId() + "");
    getMapParameter(sql, map);
    int count = jdbcTemplate.update(sql.toString(), map);
    return count > 0;
  }

  @Override
  public boolean update(BillFee billFee, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("update chf_bill_fee set ");
    sql.append("type=:type,fee_name=:fee_name,standard=:standard,fee_modules=:fee_modules ");
    sql.append(",invoicestate=:invoicestate,invoicenum=:invoicenum,currency=:currency,exchange_rate=:exchange_rate ");
    sql.append(",price=:price,remark=:remark,update_user=:update_user,update_time=now() where id=:id ");
    map.put("type", billFee.getType());
    map.put("standard", billFee.getStandard());
    map.put("fee_modules", billFee.getFeeModules2());
    map.put("invoicestate", billFee.getInvoicestate());
    map.put("invoicenum", billFee.getInvoicenum());
    map.put("currency", billFee.getCurrency());
    map.put("exchange_rate", billFee.getExchangeRate());
    map.put("fee_name", billFee.getFeeName());
    map.put("price", billFee.getPrice());
    map.put("remark", billFee.getRemark());
    map.put("update_user", loginUser.getUserId() + "");
    map.put("id", billFee.getId());
    getMapParameter(sql, map);
    int count = jdbcTemplate.update(sql.toString(), map);
    return count > 0;
  }

  @Override
  public boolean updateFeeState(BillFee billFee, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("update chf_bill_fee set ");
    sql.append("fee_state=:fee_state,fee_person=:fee_person,fee_time=:fee_time ");
    sql.append(",update_user=:update_user,update_time=now() where id=:id ");
    map.put("fee_state", billFee.getFeeState());
    map.put("fee_person", billFee.getFeePerson());
    map.put("fee_time", billFee.getFeeTime());
    map.put("update_user", loginUser.getUserId() + "");
    map.put("id", billFee.getId());
    getMapParameter(sql, map);
    int count = jdbcTemplate.update(sql.toString(), map);
    return count > 0;
  }

  @Override
  public boolean updateFeeStateByBill(BillFee billFee, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("update chf_bill_fee set ");
    sql.append("fee_state=:fee_state,fee_person=:fee_person,fee_time=:fee_time ");
    sql.append(",update_user=:update_user,update_time=now() where bill_id=:bill_id ");
    if (Utils.isNotEmpty(billFee.getType())) {
      sql.append("and type=:type ");
      map.put("type", billFee.getType());
    }
    map.put("fee_state", billFee.getFeeState());
    map.put("fee_person", billFee.getFeePerson());
    map.put("fee_time", billFee.getFeeTime());
    map.put("update_user", loginUser.getUserId() + "");
    map.put("bill_id", billFee.getBillId());

    getMapParameter(sql, map);
    int count = jdbcTemplate.update(sql.toString(), map);
    return count > 0;
  }


  @Override
  public boolean updateWriteOffAccounts(BillFeeWriteOffItem billfee, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("update chf_bill_fee set ");
    sql.append(" settlement=:settlement ");
    sql.append(" ,settlement_price=:lastPrice ");
//		if(Utils.isNotEmpty(bill.getWriteOffAccountsPriceYs())){
//			sql.append(" write_off_accounts_price_ys=:write_off_accounts_price_ys ");
//			map.put("write_off_accounts_price_ys", bill.getWriteOffAccountsPriceYs());
//		}
//		if(Utils.isNotEmpty(bill.getWriteOffAccountsTypeYs())){
//			sql.append(" ,write_off_accounts_type_ys=:write_off_accounts_type_ys ");
//			map.put("write_off_accounts_type_ys", bill.getWriteOffAccountsTypeYs());
//		}
//		if(Utils.isNotEmpty(bill.getWriteOffAccountsPriceYf())){
//			sql.append(" write_off_accounts_price_yf=:write_off_accounts_price_yf ");
//			map.put("write_off_accounts_price_yf", bill.getWriteOffAccountsPriceYf());
//		}
//		if(Utils.isNotEmpty(bill.getWriteOffAccountsTypeYf())){
//			sql.append(" ,write_off_accounts_type_yf=:write_off_accounts_type_yf ");
//			map.put("write_off_accounts_type_yf", bill.getWriteOffAccountsTypeYf());
//		}
    sql.append(" ,update_user=:update_user, update_time=now() ");
    sql.append("where bill_id =:bill_id and invoicename=:invoicename and type=:type ");
    map.put("update_user", loginUser.getUserId() + "");
    map.put("settlement", billfee.getSettlement());
    map.put("lastPrice", billfee.getLastPrice());
    map.put("bill_id", billfee.getBillId());
    map.put("invoicename", billfee.getInvoicename());
    map.put("type", billfee.getType());
    getMapParameter(sql, map);
    int count = jdbcTemplate.update(sql.toString(), map);
    return count > 0;
  }

  @Override
  public boolean remove(BillFee billFee, User loginUser) {
    int count = 0;
    Map<String, String> map = null;
    if (billFee.getId().length() > 0) {
      for (String ID : billFee.getId().split(",")) {
        map = new HashMap<String, String>();
        if (ID == null || ID.equals("")) {
          break;
        }
        StringBuffer sql = new StringBuffer("update chf_bill_fee set deleted=1" +
                ",update_user=:update_user, update_time=now() "
                + " where id =:id ");
        map.put("update_user", loginUser.getUserId() + "");
        map.put("id", ID);
        getMapParameter(sql, map);
        count = jdbcTemplate.update(sql.toString(), map);
      }
    }
    return count > 0;
  }
}
